package com.b2c.repository.oms;

import com.b2c.entity.datacenter.DcShopEntity;
import com.b2c.entity.datacenter.DcTmallOrderAddressEntity;
import com.b2c.entity.datacenter.DcTmallOrderEntity;
import com.b2c.entity.datacenter.DcTmallOrderItemEntity;
import com.b2c.entity.douyin.DcDouyinOrdersItemsEntity;
import com.b2c.entity.douyin.DcDouyinOrdersListVo;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.erp.ErpSalesOrderEntity;
import com.b2c.entity.erp.ErpSalesOrderItemEntity;
import com.b2c.entity.erp.enums.ErpOrderSourceEnum;
import com.b2c.entity.kwai.DcKwaiOrderEntity;
import com.b2c.entity.kwai.DcKwaiOrdersItemEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.enums.EnumErpOrderSendStatus;
import com.b2c.entity.enums.erp.EnumErpOrderStatus;
import com.b2c.entity.enums.mall.EnumOrderSendStatus;
import com.b2c.entity.enums.third.EnumDouYinOrderStatus;
import com.b2c.entity.enums.third.EnumKwaiOrderStatus;
import com.b2c.repository.Tables;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * 描述：
 * 订单确认Repository
 *
 * @author qlp
 * @date 2019-11-20 09:22
 */
@Repository
public class OrderConfirmRepository {
    @Autowired
    JdbcTemplate jdbcTemplate;
    private static Logger log = LoggerFactory.getLogger(OrderConfirmRepository.class);
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

   
    /**
     * 订单确认并加入到仓库发货队列（202209新）
     *
     * @param orderId
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> orderConfirmAndJoinDeliveryQueueForTao(Long orderId, Integer clientId, String receiver, String mobile, String address, String remark) {
        log.info("订单确认并加入到仓库发货队列 orderConfirmAndJoinDeliveryQueueForTao");
        /**********0、查询订单并做数据判断**********/
        //查询订单
        var orders = jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrder + " WHERE id=?", new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), orderId);
        if (orders == null) return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");//订单不存在

        DcTmallOrderEntity order = orders.get(0);

        Integer erpOrderCount = jdbcTemplate.queryForObject("SELECT count(0) FROM erp_order_send WHERE orderSn=? AND shopId=?",Integer.class, order.getId(),order.getShopId());
        if(erpOrderCount>0)return new ResultVo<>(EnumResultVo.ParamsError, "仓库已经存在该订单");

        //供应商id
        Integer supplierId = 0;

        // //将订单卖家备注修改为当前值
        // if(!StringUtils.isEmpty(sellerMemo))order.setRemark(sellerMemo);
        // order.setReceiver_name(receiver);
        // order.setReceiver_phone(mobile);
        // order.setAddress(address);

        //查询订单item
        var orderItems = jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrderItem + " WHERE orderId=?", new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class), orderId);

        //库存检查
        for (var item : orderItems) {
            if(StringUtils.isEmpty(item.getSpecNumber())){
                return new ResultVo<>(EnumResultVo.DataError, "订单商品没有SKU信息");
            }
            //查询erp商品规格信息
            ErpGoodsSpecEntity erpGoodsSpec = null;
            try {
                erpGoodsSpec = jdbcTemplate.queryForObject("select spec.*,eg.number as goodsNumber,eg.`name` as goodTitle,eg.erpContactId from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSpecNumber());
                supplierId = erpGoodsSpec.getErpContactId();
            } catch (Exception E) {
                return new ResultVo<>(EnumResultVo.DataError, "SKU【" + item.getSpecNumber() + "】不存在");
            }

/*            var sku = jdbcTemplate.queryForObject("select IFNULL(SUM(currentQty),0) AS currentQty,IFNULL(SUM(lockedQty),0) AS lockedQty from " + Tables.ErpGoodsStockInfo + "  WHERE specId=? AND isDelete = 0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), erpGoodsSpec.getId());
            //真实库存=当前库存-(拣货中)锁定库存-待拣货库存
            Integer djhCount = jdbcTemplate.queryForObject(" SELECT IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=? AND (STATUS=0 OR STATUS=1) ),0) djhCount", Integer.class, erpGoodsSpec.getId());
            int currentQty = sku.getCurrentQty().intValue() - (sku.getLockedQty().intValue() + djhCount.intValue());
            if (item.getQuantity().intValue() > currentQty)
                return new ResultVo<>(EnumResultVo.ParamsError, "仓库商品【" + item.getGoodsSpecNum() + "】库存不足");*/

            //反向补充pdd_orders_item中商品信息
            String updItemSQL="update "+Tables.DcTmallOrderItem+" set erpGoodsId=?,erpGoodsSpecId=? where id=?";
            jdbcTemplate.update(updItemSQL,erpGoodsSpec.getGoodsId(),erpGoodsSpec.getId(),item.getId());

            //重新设置规格id为erp_goods_spec_id
            //重新设置商品id为erp_goods_id
            item.setGoodsNumber(erpGoodsSpec.getGoodsNumber());
            item.setGoodsTitle(erpGoodsSpec.getGoodTitle());
            item.setErpGoodsId(erpGoodsSpec.getGoodsId());
            item.setErpGoodsSpecId(erpGoodsSpec.getId());
            item.setGoodsPurPrice(erpGoodsSpec.getPurPrice());
            String goodsSpec = erpGoodsSpec.getColorValue()+" "+erpGoodsSpec.getSizeValue()+ " "+ erpGoodsSpec.getStyleValue();
            item.setSkuInfo(goodsSpec);
        }
        //查询店铺信息
        var shop = jdbcTemplate.queryForObject("select * from dc_shop where id=?", new BeanPropertyRowMapper<>(DcShopEntity.class), order.getShopId());

        //收货地址
        var addrList  = jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrderAddress + " WHERE orderId=?", new BeanPropertyRowMapper<>(DcTmallOrderAddressEntity.class), orderId);
        var addr = addrList.get(0);

        try {
            /**********1.1、开始插入仓库系统订单表erp_order**********/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO erp_order_send");
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" orderSn=?,");
            orderInsertSQL.append(" totalAmount=?,");
            orderInsertSQL.append(" shippingFee=?,");
            orderInsertSQL.append(" orderTime=?,");
            orderInsertSQL.append(" logisticsCompany=?, ");
            orderInsertSQL.append(" logisticsCode=?, ");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" consignee=?,");
            orderInsertSQL.append(" mobile=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" area=?,");
            orderInsertSQL.append(" street=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" sendStatus=?,");
            orderInsertSQL.append(" supplierId=?,");
            orderInsertSQL.append(" shopId=?,");
            orderInsertSQL.append(" shopType=?");
            
            final Integer finalsupplierId = supplierId;
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getId());
                    ps.setBigDecimal(2, order.getPayAmount());
                    ps.setBigDecimal(3, order.getShippingFee());
                    ps.setString(4,order.getCreateTime().toString());
                    ps.setString(5, order.getLogisticsCompany());
                    ps.setString(6,  order.getLogisticsCode());
                    ps.setString(7, remark);
                    ps.setString(8, receiver);
                    ps.setString(9, mobile);
                    ps.setString(10, addr.getProvince());
                    ps.setString(11, addr.getCity());
                    ps.setString(12, addr.getArea());
                    ps.setString(13, addr.getTown());
                    ps.setString(14, address);
                    ps.setInt(15, 0);
                    ps.setInt(16,finalsupplierId);
                    ps.setInt(17, shop.getId());
                    ps.setInt(18, shop.getType());
                    return ps;
                }
            }, keyHolder);

            Long erpOrderSendId = keyHolder.getKey().longValue();

            /**********1.2、开始插入仓库系统订单明细表erp_order_item**********/
            StringBuilder orderItemInsertSQL = new StringBuilder();
            orderItemInsertSQL.append("INSERT INTO erp_order_send_item ");
            orderItemInsertSQL.append(" SET ");
            orderItemInsertSQL.append(" orderSendId=?,");
            orderItemInsertSQL.append(" goodsName=?,");
            orderItemInsertSQL.append(" goodsId=?,");
            orderItemInsertSQL.append(" goodsNumber=?,");
            orderItemInsertSQL.append(" goodsSpecNumber=?,");
            orderItemInsertSQL.append(" goodsSpec=?,");
            orderItemInsertSQL.append(" goodsSpecId=?,");
            orderItemInsertSQL.append(" goodsImg=?,");
            orderItemInsertSQL.append(" quantity=?,");
            orderItemInsertSQL.append(" status=?,");
            orderItemInsertSQL.append(" itemAmount=?,");
            orderItemInsertSQL.append(" price=?,");
            orderItemInsertSQL.append(" purPrice=?");

            Integer totalQuantity = 0;//商品总数

            for (var item : orderItems) {
                jdbcTemplate.update(orderItemInsertSQL.toString(),
                        erpOrderSendId,
                        item.getGoodsTitle(),
                        item.getErpGoodsId(),//这个字段的值已经变成了仓库系统的goodsId
                        item.getGoodsNumber(),
                        item.getSpecNumber(),
                        item.getSkuInfo(),
                        item.getErpGoodsSpecId(),
                        item.getProductImgUrl(),
                        item.getQuantity(),
                        EnumErpOrderSendStatus.WaitOut.getIndex(),
                        item.getItemAmount(),
                        item.getPrice(),
                        item.getGoodsPurPrice()
                );

                totalQuantity += item.getQuantity().intValue();

            }

            /**********3、更新pdd订单auditStatus状态为1**********/
            jdbcTemplate.update("UPDATE " + Tables.DcTmallOrder + " SET auditStatus=1,remark=?,tag=1 WHERE id=?",remark,orderId);

        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "失败" + e.getMessage());
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }





    /**
     * 订单确认
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> douyinOrderAffirm(Long dcDouyinOrdersId,Integer clientId, String receiver, String mobile, String address, String sellerMemo){


//        var listss = jdbcTemplate.query("SELECT o.order_id,o.logistics_code,i.code from dc_douyin_orders o LEFT JOIN dc_douyin_orders_items i on o.id=i.dc_douyin_orders_id WHERE auditStatus=0 and LENGTH(i.code)>5 and LENGTH(o.logistics_code)>0",new BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class));
        log.info("订单确认douyinOrderAffirm--将发过货的抖店订单确认到仓库");
//        var t = listss.stream().filter(s->s.getOrderId().equals("4905591970523330328")).findFirst().get();
//        var or = t.getOrderId();
        DcDouyinOrdersListVo order = null;
        try {
            order = jdbcTemplate.queryForObject("select * from dc_douyin_orders where order_id=?", new BeanPropertyRowMapper<>(DcDouyinOrdersListVo.class),dcDouyinOrdersId);
            order.setItems(jdbcTemplate.query("select * from dc_douyin_orders_items where dc_douyin_orders_id=? and item_status <> 4",new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class),order.getId()));
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误,订单不存在");
        }
   
        if(order.getOrderStatus()!=EnumDouYinOrderStatus.WAIT_SEND_GOODS.getThirdIndex())
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误,订单不是待发货状态");

        if(order.getAuditStatus()==1)return new ResultVo<>(EnumResultVo.ParamsError,"参数错误,订单已确认过");


        Integer erpOrderCount = jdbcTemplate.queryForObject("select count(0) from erp_order_send where orderSn=? AND shopId=?",Integer.class,order.getOrderId(),order.getShopId());

        if(erpOrderCount>0) return new ResultVo<>(EnumResultVo.ParamsError,"订单已确认过了");

            Integer supplierId = 0;
            //库存检查
            for (var item : order.getItems()) {
                if(StringUtils.isEmpty(item.getSpecCode()))return new ResultVo<>(EnumResultVo.DataError, "订单商品没有SKU信息");

                //查询erp商品规格信息
                ErpGoodsSpecEntity erpGoodsSpec = null;
                
                try {
                    String sq = "select spec.*,eg.number as goodsNumber,eg.`name` as goodTitle,eg.erpContactId from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1";
                    erpGoodsSpec = jdbcTemplate.queryForObject(sq, new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSpecCode());

                    supplierId = erpGoodsSpec.getErpContactId();
                } catch (Exception E) {
                    return new ResultVo<>(EnumResultVo.DataError, "SKU【" + item.getSpecCode() + "】不存在");
                }
                /**
                var sku = jdbcTemplate.queryForObject("select IFNULL(SUM(currentQty),0) AS currentQty,IFNULL(SUM(lockedQty),0) AS lockedQty from " + Tables.ErpGoodsStockInfo + "  WHERE specId=? AND isDelete = 0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), erpGoodsSpec.getId());

                //真实库存=当前库存-(拣货中)锁定库存-待拣货库存
                Integer djhCount = jdbcTemplate.queryForObject(" SELECT IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=? AND (STATUS=0 OR STATUS=1) ),0) djhCount", Integer.class, erpGoodsSpec.getId());
                int currentQty = sku.getCurrentQty().intValue() -  djhCount.intValue();
                if (item.getComboNum().intValue() > currentQty){
                    jdbcTemplate.update("UPDATE dc_douyin_orders SET auditStatus=0 WHERE id=?",order.getId());
                    flag=true;
                    log.info(obj.getOrderId()+"仓库商品【" + item.getCode() + "】库存不足");
                    //return new ResultVo<>(EnumResultVo.ParamsError, "仓库商品【" + item.getCode() + "】库存不足");
                }
                **/
                //反向补充pdd_orders_item中商品信息
                String updItemSQL="update dc_douyin_orders_items set erpGoodsId=?,erpGoodsSpecId=? where id=?";
                jdbcTemplate.update(updItemSQL,erpGoodsSpec.getGoodsId(),erpGoodsSpec.getId(),item.getId());

                //重新设置规格id为erp_goods_spec_id
                //重新设置商品id为erp_goods_id
                item.setGoodsNumber(erpGoodsSpec.getGoodsNumber());
                item.setErpGoodsId(erpGoodsSpec.getGoodsId());
                item.setErpGoodsSpecId(erpGoodsSpec.getId());
                item.setSpecCode(erpGoodsSpec.getSpecNumber());
                item.setErpGoodsName(erpGoodsSpec.getGoodTitle());
                item.setGoodsPurPrice(erpGoodsSpec.getPurPrice());
                String goodsSpec = erpGoodsSpec.getColorValue()+" "+erpGoodsSpec.getSizeValue()+ " "+ erpGoodsSpec.getStyleValue();
                item.setSkuInfo(goodsSpec);
                item.setSpec(goodsSpec);
                item.setErpGoodsSpecImg(erpGoodsSpec.getColorImage());
            }

        //查询店铺信息
        var shop = jdbcTemplate.queryForObject("select * from dc_shop where id=?", new BeanPropertyRowMapper<>(DcShopEntity.class), order.getShopId());

        final DcDouyinOrdersListVo finalOrder = order;
        try {
            /**********1.1、开始插入仓库系统订单表erp_order**********/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO erp_order_send");
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" orderSn=?,");
            orderInsertSQL.append(" totalAmount=?,");
            orderInsertSQL.append(" shippingFee=?,");
            orderInsertSQL.append(" orderTime=?,");
            orderInsertSQL.append(" logisticsCompany=?, ");
            orderInsertSQL.append(" logisticsCode=?, ");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" consignee=?,");
            orderInsertSQL.append(" mobile=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" area=?,");
            orderInsertSQL.append(" street=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" sendStatus=?,");
            orderInsertSQL.append(" supplierId=?,");
            orderInsertSQL.append(" shopId=?,");
            orderInsertSQL.append(" shopType=?");
            
            final Integer finalsupplierId = supplierId;
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, finalOrder.getOrderId());
                    ps.setDouble(2, finalOrder.getOrderTotalAmount());
                    ps.setDouble(3, finalOrder.getPostAmount());
                    ps.setString(4,finalOrder.getCreateTime());
                    ps.setString(5, finalOrder.getLogisticsCompany());
                    ps.setString(6,  finalOrder.getLogisticsCode());
                    ps.setString(7, sellerMemo);
                    ps.setString(8, receiver);
                    ps.setString(9, mobile);
                    ps.setString(10, finalOrder.getProvince());
                    ps.setString(11, finalOrder.getCity());
                    ps.setString(12, finalOrder.getTown());
                    ps.setString(13, finalOrder.getStreet());
                    ps.setString(14, address);
                    ps.setInt(15, 0);
                    ps.setInt(16,finalsupplierId);
                    ps.setInt(17, shop.getId());
                    ps.setInt(18, shop.getType());
                    return ps;
                }
            }, keyHolder);

            Long erpOrderSendId = keyHolder.getKey().longValue();

            /**********1.2、开始插入仓库系统订单明细表erp_order_item**********/
            StringBuilder orderItemInsertSQL = new StringBuilder();
            orderItemInsertSQL.append("INSERT INTO erp_order_send_item ");
            orderItemInsertSQL.append(" SET ");
            orderItemInsertSQL.append(" orderSendId=?,");
            orderItemInsertSQL.append(" goodsName=?,");
            orderItemInsertSQL.append(" goodsId=?,");
            orderItemInsertSQL.append(" goodsNumber=?,");
            orderItemInsertSQL.append(" goodsSpecNumber=?,");
            orderItemInsertSQL.append(" goodsSpec=?,");
            orderItemInsertSQL.append(" goodsSpecId=?,");
            orderItemInsertSQL.append(" goodsImg=?,");
            orderItemInsertSQL.append(" quantity=?,");
            orderItemInsertSQL.append(" status=?,");
            orderItemInsertSQL.append(" itemAmount=?,");
            orderItemInsertSQL.append(" price=?,");
            orderItemInsertSQL.append(" purPrice=?");

            Integer totalQuantity = 0;//商品总数

            for (var item : order.getItems()) {
                jdbcTemplate.update(orderItemInsertSQL.toString(),
                        erpOrderSendId,
                        item.getErpGoodsName(),
                        item.getErpGoodsId(),//这个字段的值已经变成了仓库系统的goodsId
                        item.getGoodsNumber(),
                        item.getSpecCode(),
                        item.getSkuInfo(),
                        item.getErpGoodsSpecId(),
                        item.getErpGoodsSpecImg(),
                        item.getComboNum(),
                        EnumErpOrderSendStatus.WaitOut.getIndex(),
                        item.getTotalAmount(),
                        item.getPrice(),
                        item.getGoodsPurPrice()
                );

                totalQuantity += item.getComboNum().intValue();

            }

            /**********3、更新pdd订单auditStatus状态为1**********/
            jdbcTemplate.update("UPDATE dc_douyin_orders SET auditStatus=1,seller_words=?,post_receiver=?,post_tel=?,post_addr=? WHERE id=?",sellerMemo,receiver,mobile,address,order.getId());

        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "失败" + e.getMessage());
        }

        
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }

    /**
     * 订单确认
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> kwaiOrderAffirm(Long dcKwaiOrdersId,Integer clientId, String receiver, String mobile, String address, String sellerMemo){
        log.info("订单确认kwaiOrderAffirm");
        Integer shopId=13;
        //查询订单
        List<DcKwaiOrderEntity> lists = jdbcTemplate.query("select * from dc_kwai_order where id=?", new BeanPropertyRowMapper<>(DcKwaiOrderEntity.class), dcKwaiOrdersId);
        lists.forEach(list->list.setItems(jdbcTemplate.query("select * from dc_kwai_orders_item where orderId=?",new BeanPropertyRowMapper<>(DcKwaiOrdersItemEntity.class),list.getId())));
        if(lists==null && lists.size()==0)return new ResultVo<>(EnumResultVo.ParamsError,"参数错误,订单不存在");
        DcKwaiOrderEntity order=lists.get(0);
        if(order.getStatus()!=EnumKwaiOrderStatus.WaitSend.getIndex())return new ResultVo<>(EnumResultVo.ParamsError,"参数错误,订单不是待发货状态");

        if(order.getAuditStatus()==1)return new ResultVo<>(EnumResultVo.ParamsError,"参数错误,订单已确认过");
        //将订单卖家备注修改为当前值
        order.setConsignee(receiver);
        order.setMobile(mobile);
        order.setAddress(address);
        order.setCreatetime(order.getCreatetime()>0?order.getCreatetime()/1000: 0);


        //库存检查
        for (var item : order.getItems()) {
            if(StringUtils.isEmpty(item.getSkunick()))return new ResultVo<>(EnumResultVo.DataError, "订单商品没有SKU信息");
            //查询erp商品规格信息
            ErpGoodsSpecEntity erpGoodsSpec = null;
            try {
                erpGoodsSpec = jdbcTemplate.queryForObject("select spec.*,(select NUMBER from erp_goods where id=spec.goodsId) goodsNumber from erp_goods_spec spec where spec.specNumber=? LIMIT 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSkunick());
            } catch (Exception E) {
                return new ResultVo<>(EnumResultVo.DataError, "SKU【" + item.getSkunick() + "】不存在");
            }
            var sku = jdbcTemplate.queryForObject("select IFNULL(SUM(currentQty),0) AS currentQty,IFNULL(SUM(lockedQty),0) AS lockedQty from " + Tables.ErpGoodsStockInfo + "  WHERE specId=? AND isDelete = 0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), erpGoodsSpec.getId());
            //真实库存=当前库存-(拣货中)锁定库存-待拣货库存
            Integer djhCount = jdbcTemplate.queryForObject(" SELECT IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=? AND (STATUS=0 OR STATUS=1) ),0) djhCount", Integer.class, erpGoodsSpec.getId());
            int currentQty = sku.getCurrentQty().intValue() - (sku.getLockedQty().intValue() + djhCount.intValue());
            if (item.getNum().intValue() > currentQty)
                return new ResultVo<>(EnumResultVo.ParamsError, "仓库商品【" + item.getSkunick() + "】库存不足");


            //重新设置规格id为erp_goods_spec_id
            //重新设置商品id为erp_goods_id
            item.setGoodsnum(erpGoodsSpec.getGoodsNumber());
            item.setErpgoodsId(erpGoodsSpec.getGoodsId());
            item.setErpgoodsSpecid(erpGoodsSpec.getId());
            item.setSkunick(erpGoodsSpec.getSpecNumber());
            item.setGoodsspec(erpGoodsSpec.getColorValue()+","+erpGoodsSpec.getSizeValue());
        }

        /**********1.1、开始插入仓库系统订单表erp_order**********/
        StringBuilder orderInsertSQL = new StringBuilder();
        orderInsertSQL.append("INSERT INTO ").append(Tables.ErpOrder);
        orderInsertSQL.append(" SET ");
        orderInsertSQL.append(" order_num=?,");
        orderInsertSQL.append(" totalAmount=?,");
        orderInsertSQL.append(" shippingFee=?,");
        orderInsertSQL.append(" orderTime=?,");
        orderInsertSQL.append(" createTime=?,");
        orderInsertSQL.append(" modifyTime=?,");
        orderInsertSQL.append(" confirmedTime=?,");
        orderInsertSQL.append(" remark=?,");
        orderInsertSQL.append(" buyerFeedback=?,");
        orderInsertSQL.append(" sellerMemo=?,");
        orderInsertSQL.append(" contactPerson=?,");
        orderInsertSQL.append(" mobile=?,");
        orderInsertSQL.append(" province=?,");
        orderInsertSQL.append(" city=?,");
        orderInsertSQL.append(" area=?,");
        orderInsertSQL.append(" areaCode=?,");
        orderInsertSQL.append(" town=?,");
        orderInsertSQL.append(" townCode=?,");
        orderInsertSQL.append(" address=?,");
        orderInsertSQL.append(" source=?,");
        orderInsertSQL.append(" status=?,");
        orderInsertSQL.append(" shopId=?");

        Integer salesOrderId = jdbcTemplate.queryForObject("SELECT IFNULL((SELECT id from erp_sales_order where orderNum=? and shopId=?),0) id",Integer.class,order.getOid(),shopId);

        try {
            double orderTotalAmount=order.getExpressfee().doubleValue()+order.getTotalfee().doubleValue();

            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, String.valueOf(order.getOid()));
                    ps.setBigDecimal(2, BigDecimal.valueOf(orderTotalAmount));
                    ps.setBigDecimal(3, order.getExpressfee());
                    ps.setLong(4, order.getCreatetime());
                    ps.setLong(5, System.currentTimeMillis() / 1000);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setLong(7, System.currentTimeMillis() / 1000);
                    ps.setString(8, "");
                    ps.setString(9, order.getBuyerRemark());
                    ps.setString(10, sellerMemo);
                    ps.setString(11, order.getConsignee());
                    ps.setString(12, order.getMobile());
                    ps.setString(13, "");
                    ps.setString(14, "");
                    ps.setString(15, "");
                    ps.setString(16,"");
                    ps.setString(17, "");
                    ps.setString(18, "");
                    ps.setString(19, order.getAddress());
                    ps.setString(20, ErpOrderSourceEnum.KWAI.getIndex());
                    ps.setInt(21, EnumErpOrderSendStatus.WaitOut.getIndex());
                    ps.setInt(22,shopId);
                    return ps;
                }
            }, keyHolder);

            Long erpOrderId = keyHolder.getKey().longValue();

            /**********1.2、开始插入仓库系统订单明细表erp_order_item**********/
            StringBuilder orderItemInsertSQL = new StringBuilder();
            orderItemInsertSQL.append("INSERT INTO ").append(Tables.ErpOrderItem);
            orderItemInsertSQL.append(" SET ");
            orderItemInsertSQL.append(" orderId=?,");
            orderItemInsertSQL.append(" aliSubItemID=?,");
            orderItemInsertSQL.append(" productMallName=?,");
            orderItemInsertSQL.append(" productId=?,");
            orderItemInsertSQL.append(" productNumber=?,");
            orderItemInsertSQL.append(" skuNumber=?,");
            orderItemInsertSQL.append(" skuId=?,");
            orderItemInsertSQL.append(" productImgUrl=?,");
            orderItemInsertSQL.append(" quantity=?,");
            orderItemInsertSQL.append(" status=?,");
            orderItemInsertSQL.append(" itemAmount=?");

            for (var item : order.getItems()) {
                jdbcTemplate.update(orderItemInsertSQL.toString(),
                        erpOrderId,
                        item.getId(),
                        item.getItemtitle(),
                        item.getErpgoodsId(),//这个字段的值已经变成了仓库系统的goodsId
                        item.getGoodsnum(),
                        item.getSkunick(),
                        item.getErpgoodsSpecid(),
                        item.getItempicurl(),
                        item.getNum().longValue(),
                        EnumErpOrderSendStatus.WaitOut.getIndex(),
                        item.getPrice().doubleValue()*item.getNum()
                );
            }
            /**********3、更新pdd订单auditStatus状态为1**********/
            String sellerWords =order.getSellerRemark()+",平台："+sellerMemo;
            jdbcTemplate.update("UPDATE dc_kwai_order SET auditStatus=1,consignee=?, mobile=?,address=?,sellerRemark=? WHERE id=?",receiver,mobile,address,sellerWords,order.getId());

        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "系统异常："+e.getMessage());
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }
    /**
     * ERP订单确认并加入到仓库发货队列
     *
     * @param orderId
     * @return
     */
    @Transactional
    public ResultVo<Integer> orderConfirmAndJoinDeliveryQueueForSales(Long orderId, String receiver, String mobile, String address, String sellerMemo) {
        log.info("ERP订单确认并加入到仓库发货队列 orderConfirmAndJoinDeliveryQueueForSales{orderId:"+orderId+"}");
        /**********0、查询订单并做数据判断**********/
        //查询订单
        var orders = jdbcTemplate.query("SELECT * FROM " + Tables.ErpSalesOrder + " WHERE id=?", new BeanPropertyRowMapper<>(ErpSalesOrderEntity.class), orderId);
        if (orders == null) return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");// -404;//订单不存在

        var order = orders.get(0);

        if (order.getAuditStatus().intValue() != 0)
            return new ResultVo<>(EnumResultVo.StateError, "订单已经确认过了");// -403;//订单已经确认过了

        if (order.getStatus() != EnumErpOrderStatus.WaitSend.getIndex())
            return new ResultVo<>(EnumResultVo.StateError, "订单不是待发货状态，不能确认");// -403;//订单不是待发货状态，不能确认

        if (order.getDeliveredStatus() != EnumOrderSendStatus.WaitOut.getIndex())
            return new ResultVo<>(EnumResultVo.StateError, "订单正在出库中");//-503;//订单出库中

        //查询订单item
        var orderItems = jdbcTemplate.query("SELECT * FROM " + Tables.ErpSalesOrderItem + " WHERE orderId=?", new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class), orderId);


        /************检查订单items库存************/
        //库存检查
        for (var item : orderItems) {
            if (StringUtils.isEmpty(item.getSpecNumber())) {
                //没有SKU
                return new ResultVo<>(EnumResultVo.ParamsError, "订单缺少SKU编码信息");
            }

            //查询erp商品规格信息
            ErpGoodsSpecEntity erpGoodsSpec = null;
            try {
                erpGoodsSpec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSpecNumber());
            } catch (Exception E) {
                return new ResultVo<>(EnumResultVo.DataError, "SKU【" + item.getSpecNumber() + "】不存在");
            }


            var sku = jdbcTemplate.queryForObject("select IFNULL(SUM(currentQty),0) AS currentQty,IFNULL(SUM(lockedQty),0) AS lockedQty from " + Tables.ErpGoodsStockInfo + "  WHERE specId=? AND isDelete = 0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), erpGoodsSpec.getId());
//            if (sku.getCurrentQty() == null ) return new ResultVo<>(EnumResultVo.ParamsError, "仓库库存没有("+specNumber+")该SKU");
            //真实库存=当前库存-(拣货中)锁定库存-待拣货库存
            Integer djhCount = jdbcTemplate.queryForObject(" SELECT IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=? AND (STATUS=0 OR STATUS=1) ),0) djhCount", Integer.class, erpGoodsSpec.getId());
            int currentQty = sku.getCurrentQty().intValue() - (sku.getLockedQty().intValue() + djhCount.intValue());
            if (item.getQuantity().intValue() > currentQty)
                return new ResultVo<>(EnumResultVo.ParamsError, "仓库商品【" + item.getSpecNumber() + "】库存不足，总库存："+sku.getCurrentQty()+",等待拣货的数量："+djhCount+"，当前数量："+item.getQuantity());

//            var spec = jdbcTemplate.queryForObject("select spec.id,spec.goodsId,(select NUMBER from erp_goods where id=spec.goodsId) goodsNumber from erp_goods_spec spec where spec.specNumber=? LIMIT 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), specNumber);
            item.setSpecId(erpGoodsSpec.getId());//重新设置规格id为erp_goods_spec_id
            item.setGoodsId(erpGoodsSpec.getGoodsId());//重新设置商品id为erp_goods_id
//            item.setGoodsNumber(erpGoodsSpec.getGoodsNumber());
        }

        /************更新订单收货地址、卖家备注************/
        //更新订单数据
        jdbcTemplate.update("UPDATE " + Tables.ErpSalesOrder + " SET sellerMemo=?,contactPerson=?,contactMobile=?,address=? WHERE id=?", sellerMemo, receiver, mobile, address, orderId);

        order.setSellerMemo(sellerMemo);
        order.setAddress(address);
        order.setContactPerson(receiver);
        order.setContactMobile(mobile);

        try {
            /**********1、开始插入仓库系统订单表erp_order**********/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO ").append(Tables.ErpOrder);
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" order_num=?,");
            orderInsertSQL.append(" totalAmount=?,");
            orderInsertSQL.append(" shippingFee=?,");
            orderInsertSQL.append(" orderTime=?,");
            orderInsertSQL.append(" createTime=?,");
            orderInsertSQL.append(" modifyTime=?,");
            orderInsertSQL.append(" confirmedTime=?,");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" buyerFeedback=?,");
            orderInsertSQL.append(" sellerMemo=?,");
            orderInsertSQL.append(" contactPerson=?,");
            orderInsertSQL.append(" mobile=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" area=?,");
            orderInsertSQL.append(" areaCode=?,");
            orderInsertSQL.append(" town=?,");
            orderInsertSQL.append(" townCode=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" source=?,");
            orderInsertSQL.append(" status=?,");
            orderInsertSQL.append(" shopId=?,");
            orderInsertSQL.append(" sale_type=?");

            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderNum());
                    ps.setBigDecimal(2, order.getTotalAmount());
                    ps.setBigDecimal(3, order.getShippingFee());
                    ps.setLong(4, order.getOrderTime());
                    ps.setLong(5, System.currentTimeMillis() / 1000);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setLong(7, System.currentTimeMillis() / 1000);
                    ps.setString(8, "");
                    ps.setString(9, order.getBuyerFeedback());
                    ps.setString(10, order.getSellerMemo());
                    ps.setString(11, order.getContactPerson());
                    ps.setString(12, order.getContactMobile());
                    ps.setString(13, order.getProvince());
                    ps.setString(14, order.getCity());
                    ps.setString(15, order.getArea());
                    ps.setString(16, "");
                    ps.setString(17, "");
                    ps.setString(18, "");
                    ps.setString(19, order.getAddress());
                    ps.setString(20, ErpOrderSourceEnum.Sales.getIndex());
                    ps.setInt(21, EnumErpOrderSendStatus.WaitOut.getIndex());
                    ps.setInt(22, order.getShopId());
                    ps.setInt(23, order.getSaleType());
                    return ps;
                }
            }, keyHolder);

            Long erpOrderId = keyHolder.getKey().longValue();
            /**********2、开始插入仓库系统订单明细表erp_order_item**********/
            StringBuilder orderItemInsertSQL = new StringBuilder();
            orderItemInsertSQL.append("INSERT INTO ").append(Tables.ErpOrderItem);
            orderItemInsertSQL.append(" SET ");
            orderItemInsertSQL.append(" orderId=?,");
            orderItemInsertSQL.append(" aliSubItemID=?,");
            orderItemInsertSQL.append(" productMallName=?,");
            orderItemInsertSQL.append(" productId=?,");
            orderItemInsertSQL.append(" productNumber=?,");
            orderItemInsertSQL.append(" skuNumber=?,");
            orderItemInsertSQL.append(" skuId=?,");
            orderItemInsertSQL.append(" productImgUrl=?,");
            orderItemInsertSQL.append(" quantity=?,");
            orderItemInsertSQL.append(" status=?,");
            orderItemInsertSQL.append(" itemAmount=?");

            for (var item : orderItems) {
                jdbcTemplate.update(orderItemInsertSQL.toString(),
                        erpOrderId,
                        item.getId(),
                        item.getGoodsTitle(),
                        item.getGoodsId(),//这个字段的值已经变成了仓库系统的goodsId
                        item.getGoodsNumber(),
                        item.getSpecNumber(),
                        item.getSpecId(),
                        item.getGoodsImage(),
                        item.getQuantity().longValue(),
                        EnumErpOrderSendStatus.WaitOut.getIndex(),
                        item.getItemAmount()
                );
            }

            /**********3、更新订单auditStatus状态为1**********/
            jdbcTemplate.update("UPDATE " + Tables.ErpSalesOrder + " SET auditStatus=1 WHERE id=?", orderId);

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");

        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "失败" + e.getMessage());
        }

    }

    public void cancelOrder(Long orderId) {
         jdbcTemplate.update("UPDATE "+Tables.ErpSalesOrder+" SET status=?,sellerMemo=?,modifyOn=? WHERE id=? AND shopId=99",EnumErpOrderStatus.CANCEL.getIndex(),"卖家取消",System.currentTimeMillis() / 1000 ,orderId);
    }
}
